using System;
using System.Data;
using System.Data.SqlClient;

namespace DataAccessLayer
{
	public class IdGenerator
	{
		public static long GetNextId(string tableName, SqlConnection connection)
		{

            SqlTransaction generateIdTransaction = connection.BeginTransaction(IsolationLevel.Serializable, "GenerateId");

            SqlCommand selectCmd = new SqlCommand("SELECT nextid FROM PKSequence WHERE tablename=@tablename", connection, generateIdTransaction);
            selectCmd.Parameters.Add("@tableName", SqlDbType.VarChar).Value = tableName;
            long nextId = (long)selectCmd.ExecuteScalar();

            SqlCommand updateCmd = new SqlCommand("UPDATE PKSequence SET nextid = @nextid WHERE tablename=@tablename", connection, generateIdTransaction);
            updateCmd.Parameters.Add("@nextid", SqlDbType.BigInt).Value = nextId + 1;
            updateCmd.Parameters.Add("@tablename", SqlDbType.VarChar).Value = tableName;
            updateCmd.ExecuteNonQuery();

            generateIdTransaction.Commit();

            return nextId;
		}
	}
}
